using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;

/// <summary>
/// Summary description for FeedbackDAO
/// </summary>
public class FeedbackDAO:DBAccess
{
	#region sql Commands   
    string SQL_SELECT_ALL = "select * from Feedback";
    string SQL_SELECT_BY_ID = "select * from Feedback where ID=@id";
    string SQL_INSERT = "insert into Feedback(WriterName,WriterEmail,Subject,Content, Date, IsReplied) values(@WriterName,@WriterEmail,@Subject,@Content, @Date, @IsReplied)";
    string SQL_UPDATE = "update Feedback set WriterName = @WriterName, WriterEmail = @WriterEmail, Subject = @Subject, Content = @Content, Date = @Date, IsReplied = @IsReplied where ID = @id";
    string SQL_DELETE = "delete from Feedback where ID = @id";

    string SQL_SELECT_FB_COUNT = "select count(*) from Feedback where Date>@fdate and Date<@sdate";
    #endregion

    #region fields
    string message = "";
    #endregion

    #region constructors
    public FeedbackDAO()
    {

    }
    #endregion

    #region properties
    public string Message
    {
        get { return message; }
        set { message = value; }
    }
    #endregion

    #region Methos
    /// <summary>
    /// Get feedback by id
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public Feedback GetFeedback(int id)
    {
        Feedback fed = new Feedback();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_BY_ID, conn);
            if (Connect())
            {
                command.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    fed.Id = dr.GetInt32(0);
                    fed.WriterName = dr.GetString(1);
                    fed.WriterEmail = dr.GetString(2);
                    fed.Subject = dr.GetString(3);
                    fed.Content = dr.GetString(4);
                    fed.Date = dr.GetDateTime(5);
                    fed.IsReplied = dr.GetBoolean(6);                   
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return fed;
    }
    /// <summary>
    /// Get all feedbacks 
    /// </summary>
    /// <returns></returns>
   public IList<Feedback> GetAllFeedbacks()
    {
        IList<Feedback> list = new List<Feedback>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_ALL, conn);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Feedback fb = new Feedback();
                    fb.Id = dr.GetInt32(0);
                    fb.WriterName = dr.GetString(1);
                    fb.WriterEmail = dr.GetString(2);
                    fb.Subject = dr.GetString(3);
                    fb.Content = dr.GetString(4);
                    fb.Date = dr.GetDateTime(5);
                    fb.IsReplied = dr.GetBoolean(6);
                    list.Add(fb);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
   /// <summary>
   /// Insert new feedback into database
   /// </summary>
   /// <param name="per"></param>
   /// <returns></returns>
   public int Insert(Feedback fed)
   {
       int effectedRows = 0;
       try
       {
           SqlCommand command = new SqlCommand(SQL_INSERT, conn);
           command.Parameters.AddWithValue("@WriterName", fed.WriterName);
           command.Parameters.AddWithValue("@WriterEmail", fed.WriterEmail);
           command.Parameters.AddWithValue("@Subject", fed.Subject);
           command.Parameters.AddWithValue("@Content", fed.Content);
           command.Parameters.AddWithValue("@Date", fed.Date);
           command.Parameters.AddWithValue("@IsReplied", fed.IsReplied);          
           if (Connect())
           {
               effectedRows = command.ExecuteNonQuery();                         
               Disconnect();
           }
       }
       catch (SqlException ex) { message = ex.Message; }
       catch (Exception ex) { message = ex.Message; }
       return effectedRows;
   }

   /// <summary>
   /// Update feedback
   /// </summary>
   /// <param name="per"></param>
   /// <returns></returns>
   public int Update(Feedback fed)
   {
       int effectedRows = 0;
       try
       {
           SqlCommand command = new SqlCommand(SQL_UPDATE, conn);
           command.Parameters.AddWithValue("@WriterName", fed.WriterName);
           command.Parameters.AddWithValue("@WriterEmail", fed.WriterEmail);
           command.Parameters.AddWithValue("@Subject", fed.Subject);
           command.Parameters.AddWithValue("@Content", fed.Content);
           command.Parameters.AddWithValue("@Date", fed.Date);
           command.Parameters.AddWithValue("@IsReplied", fed.IsReplied); 
           command.Parameters.AddWithValue("@id", fed.Id);           
           if (Connect())
           {
               effectedRows = command.ExecuteNonQuery();
               Disconnect();
           }
       }
       catch (SqlException ex) { message = ex.Message; }
       catch (Exception ex) { message = ex.Message; }
       return effectedRows;
   }
    /// <summary>
    /// Set a feedback at replied or not
    /// </summary>
    /// <param name="fed"></param>
    /// <returns></returns>
    public int UpdateState(int Id,bool isReplied)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand("UPDATE Feedback SET IsReplied=@isReplied WHERE ID=@id", conn);
            command.Parameters.AddWithValue("@IsReplied", isReplied);
            command.Parameters.AddWithValue("@id", Id);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }
   /// <summary>
   /// Remove feedback out of database
   /// </summary>
   /// <param name="per"></param>
   /// <returns></returns>
   public int Delete(int  Id)
   {
       int effectedRows = 0;
       try
       {
           SqlCommand command = new SqlCommand(SQL_DELETE, conn);
           command.Parameters.AddWithValue("@id", Id);
           if (Connect())
           {
               effectedRows = command.ExecuteNonQuery();
               Disconnect();
           }
       }
       catch (SqlException ex) { message = ex.Message; }
       catch (Exception ex) { message = ex.Message; }
       return effectedRows;
   }
    /// <summary>
    /// Get a feedback list with paging
    /// </summary>
    /// <param name="currentPage"></param>
    /// <param name="pageSize"></param>
    /// <param name="total"></param>
    /// <param name="keyword"></param>
    /// <returns></returns>
    public IList<Feedback> GetFeedbacks(int currentPage, int pageSize, ref int total)
    {
        IList<Feedback> list = new List<Feedback>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectFeedbacks", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Feedback fb = new Feedback();
                    fb.Id = dr.GetInt32(1);
                    fb.Subject = dr.GetString(2);
                    if(!dr.IsDBNull(3))fb.WriterEmail = dr.GetString(3);                   
                    if (!dr.IsDBNull(5)) fb.Content = dr.GetString(4);
                    fb.Date = dr.GetDateTime(5);
                    fb.IsReplied = dr.GetBoolean(6);
                    list.Add(fb);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return list;
    }

    /// <summary>
    /// Get feedback list with paging and condition
    /// </summary>
    /// <param name="currentPage"></param>
    /// <param name="pageSize"></param>
    /// <param name="total"></param>
    /// <param name="keyword">text to search</param>
    /// <returns></returns>
    public IList<Feedback> GetFeedbacksExt(int currentPage, int pageSize, ref int total, string keyword)
    {
        IList<Feedback> list = new List<Feedback>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectFeedbacksExt", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@keyword", SqlDbType.NVarChar);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@keyword"].Value = keyword;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Feedback fb = new Feedback();
                    fb.Id = dr.GetInt32(1);
                    fb.Subject = dr.GetString(2);
                    fb.WriterEmail = dr.GetString(3);
                    if (!dr.IsDBNull(4)) fb.Content = dr.GetString(4);
                    fb.Date = dr.GetDateTime(5);
                    fb.IsReplied = dr.GetBoolean(6);
                    list.Add(fb);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return list;
    }

    /// <summary>
    /// Get number of feedback in a period of time
    /// </summary>
    /// <param name="firtDate">first date</param>
    /// <param name="secondDae">second date (> first date)</param>
    /// <returns>number of feedbacks</returns>
    public int GetFeedbackCount(DateTime firstDate, DateTime secondDate)
    {
        int quantity = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_FB_COUNT, conn);
            if (Connect())
            {
                command.Parameters.AddWithValue("@fdate", firstDate);
                command.Parameters.AddWithValue("@sdate", secondDate);
                quantity = int.Parse(command.ExecuteScalar().ToString());
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return quantity;

    }
    #endregion
}
